package product;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

import javax.swing.table.DefaultTableModel;

public class SalarySQL {

	public Connection con;
	public ResultSet rs;
	public Statement st;
	public PreparedStatement psmt;
	public CallableStatement csmt;

	public SalarySQL(Connection conn) {
		this.con=conn;
	}

	//년월,부서,직책별 조회 통합 메서드
	public void search(String ym, String find, DefaultTableModel model) {
		model.setNumRows(0);
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select t.memberid, irum, team, position, pay,");
			sql.append("(select nvl(count(*),0) from chuldata t2 "
					+ "where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1),");
			sql.append("(select nvl(count(*),0) from holydata t3 "
					+ "where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0),");
			sql.append("nvl(nvl(t.pay,0)*("
					+ "(select nvl(count(*),0) from chuldata t2 "
					+ "where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1) +"); 
			sql.append("(select nvl(count(*),0) from holydata t3 "
					+ "where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0)),0) ");
			sql.append("from memberdata t ");
			if(!find.equals("")) sql.append(find);
			psmt = con.prepareStatement(sql.toString());
			rs=psmt.executeQuery();
			tablePrn(rs,model);//결과 값 모델에 저장하는 메서드
			rs.close();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//메서드들의 결과 값 모델에 저장
	public void tablePrn(ResultSet rs,DefaultTableModel model){
		try {
			while(rs.next()){
				Vector<String> vec = new Vector<String>();
				DecimalFormat df = new DecimalFormat("#,###");//콤마표시를 위해
				vec.add(rs.getString(1));//사번
				vec.add(rs.getString(3));//부서
				vec.add(rs.getString(4));//직책
				vec.add(rs.getString(2)); //성명
				vec.add(df.format(rs.getLong(5))+"원"); //일당
				vec.add(Integer.toString(rs.getInt(6))+"일");//출근일수     
				vec.add(Integer.toString(rs.getInt(7))+"일");//유급일수
				vec.add(df.format(rs.getLong(8))+"원");//일당 * 출근일수
				if(rs.getInt(6)!=0 || rs.getInt(7) !=0){
					model.addRow(vec);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//각 휴가종류 일수 표시
	public void holyTable(String id, String name, String ym, DefaultTableModel model, String date){
		
		int row=0;
		boolean dataCHK=true;
		for(int i=0; i<model.getRowCount(); i++){
			if(((String)model.getValueAt(i, 0)).equals(id) && ((String)model.getValueAt(i, 7)).equals(date)){
				dataCHK=false;
				row=i;
				break;
			}
		}
		if(dataCHK){
			StringBuilder sql = new StringBuilder();
			try {
				sql.append("select memberid , ");
				sql.append("(select count(*) from holydata h2 "
						+ "where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE=1),");
				sql.append("(select count(*) from holydata h2 "
						+ "where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE=2),");
				sql.append("(select count(*) from holydata h2 "
						+ "where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE=3),");
				sql.append("(select count(*) from holydata h2 "
						+ "where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE=4),");
				sql.append("(select count(*) from holydata h2 "
						+ "where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE!=0)");
				sql.append("from holydata h1 group by memberid having memberid = ?");
				psmt = con.prepareStatement(sql.toString());
				psmt.setString(1, id);
				rs = psmt.executeQuery();
				if(rs.next()){//결과 값이 있다면
					Vector<String> vec = new Vector<String>();
					vec.add(rs.getString(1));
					vec.add(name);
					vec.add(rs.getInt(2)+"일");
					vec.add(rs.getInt(3)+"일");
					vec.add(rs.getInt(4)+"일");
					vec.add(rs.getInt(5)+"일");
					vec.add(rs.getInt(6)+"일");
					vec.add(date);
					model.insertRow(0, vec);
					//model.addRow(vec);
				}else{//휴가 테이블에 id가 없는 경우 때문에
					Vector<String> vec = new Vector<String>();
					vec.add(id);
					vec.add(name);
					vec.add(0+"일");
					vec.add(0+"일");
					vec.add(0+"일");
					vec.add(0+"일");
					vec.add(0+"일");
					vec.add(date);
					model.insertRow(0, vec);
					//model.addRow(vec);
				}
				rs.close();
				psmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else{
			Vector<String> vec = new Vector<String>();
			vec.add((String)model.getValueAt(row, 0));
			vec.add((String)model.getValueAt(row, 1));
			vec.add((String)model.getValueAt(row, 2));
			vec.add((String)model.getValueAt(row, 3));
			vec.add((String)model.getValueAt(row, 4));
			vec.add((String)model.getValueAt(row, 5));
			vec.add((String)model.getValueAt(row, 6));
			vec.add((String)model.getValueAt(row, 7));
			model.removeRow(row);
			model.insertRow(0, vec);
		}
	}

	//사원 수 확인 메서드
	public int memberCount(){
		int mc=0;
		try {
			String sql = "select count(*) from memberdata";
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
			rs.next();
			mc = rs.getInt(1);
			rs.close();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mc;
	}

	//사번 유무 체크
	public int memCheck(String id){
		int check=0;
		try {
			String sql="select count(*) from memberdata where memberid=?";
			psmt = con.prepareStatement(sql);
			psmt.setString(1, id);
			rs = psmt.executeQuery();
			if(rs.next()){
				check = rs.getInt(1);
			}
			rs.close();
			psmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return check;
	}

	//급여 테이블 입력
	public void salary(){ //급여조회
		try {
			int end = memberCount(); //총수
			StringBuilder sql = new StringBuilder();//빌더 쓸래.

			SimpleDateFormat ye = new SimpleDateFormat("yy");
			SimpleDateFormat mon = new SimpleDateFormat("MM");
			String month = mon.format(new Date());
			String year = ye.format(new Date());

			sql.append("declare ");
			sql.append("flag number; ");
			sql.append("BEGIN ");
			sql.append("FOR i IN 1.."+(end-1)+" LOOP ");
			sql.append("select count(*) into flag from salary where memberid='A'||i and s_year="+year+"; ");
			sql.append("if flag=0 then ");
			sql.append("insert into salary(S_no,memberid,s_year,s_"+month+") values(seq_s_no.nextval,'A' || i,"+year+", ");
			sql.append("(select pay*(nvl((select count(*) from chuldata  where memberid='A' || i and chuldate like '"+year+"/"+month+"%' and chultype=1 group by memberid),0)+ ");
			sql.append("nvl((select count(*) from holydata where memberid='A' || i and holydate like '"+year+"/"+month+"%' and holytype!=0 group by memberid),0)) ");
			sql.append("from memberdata where memberid = 'A' || i)); ");
			sql.append("else ");
			sql.append("update salary set s_"+month+" = (select pay * ( ");
			sql.append("nvl((select count(*) from chuldata  where memberid='A' || i and chuldate like '"+year+"/"+month+"%' and chultype=1 group by memberid),0) + ");
			sql.append("nvl((select count(*) from holydata where memberid='A' || i and holydate like '"+year+"/"+month+"%' and holytype!=0 group by memberid),0)) ");
			sql.append("from memberdata where memberid = 'A' || i) where memberid = 'A' || i and s_year="+year+"; ");
			sql.append("END IF; ");
			sql.append("END LOOP; ");
			sql.append("END; ");

			PreparedStatement pstmt = con.prepareStatement(sql.toString());
			pstmt.execute();
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void profit(){ //수익
		StringBuilder sql = new StringBuilder();//빌더 쓸래.
		SimpleDateFormat ye = new SimpleDateFormat("yy");
		SimpleDateFormat mon = new SimpleDateFormat("MM");
		String month = mon.format(new Date());
		String year = ye.format(new Date());

		sql.append("declare ");
		sql.append("flag number; ");
		sql.append("BEGIN ");
		sql.append("select count(*) into flag from profit where p_year="+year+"; ");
		sql.append("if flag=0 then ");
		sql.append("insert into profit(p_no,p_year,p_"+month+") values(pf_serial.nextval,"+year+", ");
		sql.append(" (select nvl(sum(sa_price),0) from sale where sa_mod_date like '%"+year+"/"+month+"%' ) - ");
		sql.append("((select nvl(sum(s_"+month+"),0) from salary where s_year="+year+")+ ");
		sql.append("(select nvl(sum(pro_price * ea),0) from  code inner join delivery on pro_code = code where nal like '%"+year+"/"+month+"%'))); ");
		sql.append("else");
		sql.append(" update profit set p_"+month+"=(select nvl(sum(sa_price),0) from sale where sa_mod_date like '%"+year+"/"+month+"%') - ");
		sql.append("((select nvl(sum(s_"+month+"),0) from salary where s_year="+year+")+ ");
		sql.append("(select nvl(sum(pro_price * ea),0) from  code inner join delivery on pro_code = code where nal like '%"+year+"/"+month+"%')) ");
		sql.append("where p_year = "+year+"; ");
		sql.append("end if; ");
		sql.append("end;");

		try{
			PreparedStatement pstmt = con.prepareStatement(sql.toString());
			pstmt.execute();
			pstmt.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}

	}

	//자원 반납
	public void dbClose(){
		try {
			if(rs != null) rs.close();
			if(st != null) st.close();
			if(psmt != null) psmt.close();
			if(csmt != null) csmt.close();
			if(con != null) con.close();
		} catch (SQLException e) {}
	}

	//사번용 조회
	public void searchSabern(String ym, String find, DefaultTableModel model) {
		try {
			model.setNumRows(0);
			String [] su1 = {"01","02","03","04","05","06","07","08","09","10","11","12",""};//월
			String [] su2 = {"14","13","12","11","10","09","08","07","06","05","04",""};//년
			StringBuilder sql = new StringBuilder();
			if(ym.equals("")){
				sabernSQL(ym, find);
				tablePrn(rs,model,"전체 년/월");
				rs.close();
				psmt.close();
			}else{
				String [] a = ym.split("/");
				if(a.length==1){
					for(int i=0;i<13;i++){
						String su4=ym+su1[i];
						sabernSQL(su4, find);
						String su3="";
						if(su1[i].equals("")) su3=a[0]+"년 / 전체 월";
						else su3=ym+su1[i];
						tablePrn(rs,model,su3);
						rs.close();
						psmt.close();
					}
				}else{
					if(a[0].equals("%")){
						for(int i=0; i<12;i++){
							String su3=su2[i]+"/"+a[1]+"/";
							ym="%"+su3;
							sabernSQL(ym, find);
							String su4="";
							if(su2[i].equals("")) su4="전체 년 / "+a[1]+"월";
							else su4=su2[i]+"/"+a[1];
							tablePrn(rs,model,su4);
							rs.close();
							psmt.close();
						}
					}
					else{
						sabernSQL(ym, find);
						String  su3 =a[0]+"/"+a[1];
						tablePrn(rs,model,su3);
						rs.close();
						psmt.close();
					}
				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//사번용 쿼리
	public void sabernSQL(String ym,String find){
		StringBuilder sql = new StringBuilder();
		sql.append("select t.memberid, irum, team, position, pay,");
		sql.append("(select nvl(count(*),0) from chuldata t2 "
				+ "where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1),");
		sql.append("(select nvl(count(*),0) from holydata t3 "
				+ "where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0),");
		sql.append("nvl(nvl(t.pay,0)*("
				+ "(select nvl(count(*),0) from chuldata t2 "
				+ "where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1) +"); 
		sql.append("(select nvl(count(*),0) from holydata t3 "
				+ "where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0)),0) ");
		sql.append("from memberdata t ");
		sql.append(find);
		try {
			psmt = con.prepareStatement(sql.toString());
			rs=psmt.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		sql.delete(0, sql.length());
	}

	//메서드들의 결과 값 모델에 저장(사번용)
	public void tablePrn(ResultSet rs,DefaultTableModel model,String ym){
		try {
			if(rs.next()){
				Vector<String> vec = new Vector<String>();
				DecimalFormat df = new DecimalFormat("#,###");//콤마표시를 위해
				vec.add(rs.getString(1));//사번
				vec.add(rs.getString(3));//부서
				vec.add(rs.getString(4));//직책
				vec.add(rs.getString(2)); //성명
				vec.add(df.format(rs.getLong(5))+"원"); //일당
				vec.add(Integer.toString(rs.getInt(6))+"일");//출근일수     
				vec.add(Integer.toString(rs.getInt(7))+"일");//유급일수
				vec.add(df.format(rs.getLong(8))+"원");//일당 * 출근일수
				vec.add(ym);
				if(rs.getInt(6)!=0 || rs.getInt(7) !=0){
					model.addRow(vec);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
